'; window.popUpWin.document.write(zhtm); window.popUpWin.document.close(); // Johnny Jackson 4/28/98 } //--> Inside AutoCAD 14 -- Ch 26 -- AutoCAD SQL Environment (ASE)


Inside AutoCAD 14

Previous chapterNext chapterContents

- 26 -

AutoCAD SQL Environment (ASE)

by Jojo Guingao

AutoCAD SQL Environment (ASE) enables you to link AutoCAD objects to external database files. ASE provides an interface for a two-way data transfer between AutoCAD and external databases, and it enables AutoCAD to associate, or link, non-graphic attributes stored in external programs, such as dBase III, Oracle, and ODBC-compliant databases (such as Microsoft Access), with graphic objects in an AutoCAD drawing. ASE supports the SQL2 standard, which uses environment, catalogs, schemas, and tables instead of the original SQL model of database management system (DBMS), databases, and tables.

This chapter covers the following topics:

Introducing the AutoCAD SQL Environment (ASE)

Autodesk has been providing tools to create "intelligent" drawings for some time. Previous releases of AutoCAD provided an AutoCAD object, known as an attribute, which enables you to store and retrieve text data. As useful as this feature was, however, it was limited only to block entities. In later releases, AutoCAD introduced Extended Entity Data (EED), which enables textual data to be attached to any AutoCAD object. Extended Entity Data has two limitations. First, you can save only 16 KB of information for each object. Second, this increases the size of the electronic drawing file, slowing performance and making it more difficult when working with drawings containing thousands of objects.

Understanding the Benefits of ASE

Introducing SQL2 Terminology and Concepts

SQL2 is an international standard that compensates for some of the deficiencies in the original SQL standard. Instead of the SQL model based on DBMS, databases, and tables, SQL2 uses environments, catalogs, schemas, and tables. Figure 26.1 illustrates the relationship between these elements of SQL2.

Figure 26.1 SQL2 hierarchy shown as collections of catalogs and schema defining the databases it accesses.

In SQL1, a database management system (DBMS) controls the data. A catalog is a collection of one or more schemas, and a schema consists of one or more database tables held in one location. A table contains database information organized in rows and columns, which are referred to as records and fields.

In SQL2, the environment comprises the DBMS, the databases it can access, and the users and programs that also can access those databases. Environments are part of a hierarchy that includes catalogs, schemas, and tables. More information on using SQL can be found later in this chapter in the section "Understanding the Features and Functions of Structured Query Language (SQL)."

To take full advantage of AutoCAD ASE capabilities, you will need to set up your database properly. This process is discussed in the following section. It is important that you follow each step carefully. Missing any of the steps will result in unsuc-cessful database connection.

Setting Up Your Database

The process of connecting to a database includes the following steps:

1. Verify that ASE is installed. If you completed a full installation of AutoCAD, then ASE is installed.

2. Run the External Database Configuration Editor to configure your database.

3. In an AutoCAD session, use the ASEADMIN command to make a connection between the database with which you are working and AutoCAD.

4. In the ASEADMIN command, set your Link Path Name (LPN) to specify key columns for the current table identified by its link path.

5. Use the ASEROWS commands to create links between AutoCAD objects and records in the database. Refer to the section "Step 3: Setting Up Your Database Links with AutoCAD Objects" for more information.

6. For those who prefer to use a toolbar, make sure you enabled the External Database toolbar. If not, go to Toolbars under the View pull-down menu, and choose External Database. This is an optional step.

After the links are made to the AutoCAD objects, this data is available for use in a number of ASE functions, including those in the following list:

Once you're successful connecting your database to your AutoCAD drawing, you are unleashing the power of "intelligent drawings." Using ASE functionalities, you can combine AutoCAD drawing and database information as your search criteria for finding the right information. For example, suppose you want to show all equipment and office locations where the manufacturer is Compaq. With ASE, this is possible by executing an SQL statement to search the linked database for all occurrences of "Compaq" as the manufacturer. If found, ASE will display its corresponding linked AutoCAD objects.

The preceding steps show the general process of creating a connection between a database and AutoCAD. The remaining headings in this section contain specific procedures for making connections between AutoCAD and the following database applications:

Connecting Using dBase III

The driver for dBase III Plus is a low-level driver that uses an SQL2 interpreter to access the database. Low-level drivers interact with the data directly, which means you only need the database (.dbf) file in dBase III Plus format. You do not need to have the dBase III Plus program installed on your system to access the database files via AutoCAD. To use a dBase III Plus database in AutoCAD, you must use the External Database Configuration Editor (asicfg.exe) to state the location of the database files you plan to use.

In order for ASE to find the database files and character-definition files with which you plan to work, you must specify the database location.

Linking to a dBase III Database

The following procedures can be used to link a dBase III database. This example uses sample database (.dbf) files, which are provided in the Sample directory of AutoCAD Release 14.

The sample database files are located in c:\acad14\sample\dbf. This example is based on an installation of AutoCAD using the default directories.

Figure 26.2 How to define your environment in dBase III.

LINKING TO A DBASE III DATABASE

1. Make sure that you have installed the External Database components for AutoCAD. If you complete a full installation of AutoCAD, then these components are included. Refer to the AutoCAD Installation Guide for specific information on installation.

2. Use the External Database Configuration (asicfg.exe) to configure your database. Using Windows 95 or Windows NT 4.0, select the Start button, then choose Programs, AutoCAD R14, External Database Configuration. Using Windows 3.51, double-click on the External Database Configuration icon in the AutoCAD R14 group.

3. The External Database Configuration dialog box is displayed. Click on the Add button. This displays the Select DBMS for new Environment dialog box. Under the DBMS List, select dBaseIII. Go to the Environment Name edit box and type DB3_R14, as shown in figure 26.3. Go to the Environment Description edit box and type R14 Sample Database. Click on the OK button.

Figure 26.3 Dialog box for creating Environment Name with dBase III.

4. The Environment:DB3_R14(DB3DRV) dialog box is displayed. If the Catalog tab is not showing, select Catalog. Go to the Name edit box in the Catalog section and type Samples. Figure 26.4 shows the Environment dialog box.

Figure 26.4 The Environment dialog box where Catalog and Schemas are defined.

TIP: The name you assign to the catalog appears in the Administration dialog box (ASEADMIN command) as one of the choices of catalogs. You can create multiple catalogs, each for a particular set of databases. Pick a meaningful name for the catalog. For example, databases for facility maintenance of the 8000 Broadmoor Street Building could be identified by "Catalog = Broadmoor Street." Likewise, sample databases could be identified by "Catalog = Samples."

[BBEG]WARNING: Catalog names beginning with a numeral or a non-alphanumeric character, such as 1stfloor or firstfloor are not allowed. Numbers and non-alphanumeric characters can be used as long as they do not begin the name (example: floor1 or first floor can be used).

5. Go to the Path edit box in the Catalog section and enter the parent directory for the database file. You can use the Browse button to help identify the proper locations. In this section, you must equate the catalog name with a location of the database files on the hard drive, specifically, the parent directory that contains subdirectories in which the .DBF files are stored.

For example, if the catalog name was Broadmoor Street, and the database files were located in c:\dbf\broamoor\floor1, the catalog location would be c:\dbf\broadmoor.

6. Click on the New button to save the Catalog information.

7. Go to the Name edit box in the Schema section and type R14_DB3 as the schema name (refer to fig. 26.4).

TIP: The schema name should be a meaningful name that identifies a subset of databases or distinguishes one database from another. These schema names are listed in the ASEADMIN dialog box when you select Schemas. For example, if databases for each floor in the 8000 Broadmoor Street building are stored in separate subdirectories based on the floor level of the building, one schema could be set for each floor: "Schema = First_floor," "Schema = Second_floor," "Schema = Third_floor." It is quite acceptable to use the subdirectory name as the schema name, if this helps you to identify the databases to use with the ASE commands. For example, if each database is stored individually in separate subdirectories called Project1 and Project2, the schema names could be "Schema=project1" and "Schema=project2."

8. Go to the Path edit box in the Schema section and type DBF as the Schema Path (again, refer to fig. 26.4). The path points to the subdirectory in which the database file or files are located.

NOTE: In the preceding 8000 Broadmoor Street example, if database files exist in the c:\dbf\broadmoor\floor1 and c:\dbf\broadmoor\floor2 subdirectories, and if the catalog name is Broadmoor Street and the schema names are First_Floor and Second_Floor, then the schema locations would be floor1 and floor2.

Figure 26.5 The Common tab of the Environment dialog box is used to specify the language and default catalog and schema.

9. Click on the New button to save the schema information.

10. Select the Common tab of the Environment: DB3_R14(DB3DRV) dialog box to specify the language and the default catalog and schema (see fig. 26.5). In the Language drop-down list, scroll down the list and select English_United States.

11. Click on the OK button to accept the changes.

12. At this point, you should be back at the External Database Configuration dialog box and the environment is now defined. Click on the Test button to verify that you successfully can connect to the dBase III database. A Login dialog box is displayed. Leave the User Name edit box and the Password edit box blank. Click on the Connect button to start the test. A dialog box will indicate that you made a successful connection. On the Connection test passed dialog box, click on the Done button, then the OK button to exit the database configuration.

13. Start AutoCAD.

14. Type ASEADMIN at the Command: prompt. The Administration dialog box is displayed. Then, in the Database Objects scroll window, select your environment DB3_R14, and click on the Connect button.

15. In the Connect to Environment dialog box, enter your name and password, and then click on OK. You may choose to leave the name and password fields blank.

16. Select the Catalog button and select the catalog you want to access.

17. Select the Schema button and select the schema you want to access.

18. Select the Table button and select the table you want to access.

You have now successfully connected to dBase III.

Oracle 7 is another DBMS from which links can be made to AutoCAD objects. To use an Oracle database, you must connect to a local or remote Oracle server. The process for connecting using Oracle is provided in the following section.

Connecting Using Oracle 7

The ASI Oracle driver is a high-level driver that translates ASI SQL to the DBMS SQL. SQL statements then are passed to the DBMS engine for processing. You can use either a local or a remote Oracle server to access databases. To use an Oracle database in AutoCAD, you must define the environment in the External Database Configuration editor to specify the location of the database files you plan to use. You also must create an information schema in the Oracle 7 database so that the driver can locate the schemas and tables you want to access.

ASI uses the terms catalog, schema, and table in describing an environment, while Oracle supports the concept of owners. Table 26.1 shows the rules that Oracle drivers follow for converting terminology.

NOTE: For more information on the concept of owners, consult the Oracle Reference manual.

Table 26.1 ASI Terminology and Oracle Terminology Equivalents

ASI Terminology Oracle Terminology
Catalog name Database name or database link
Schema name Owner name
Table name Table name

In addition to containing a collection of schemas, a catalog also contains an information schema. An information schema is a special group of tables that describes the contents of the tables, including the columns in the various tables, the defined views, and the privileges associated with each authorization identification.

The following exercise details the procedure to link to an Oracle database.

LINKING TO AN ORACLE DATABASE

1. Make sure that you have installed the External Database component for AutoCAD. If you complete a full installation of AutoCAD, then this component is included. Refer to the AutoCAD Installation Guide for specific information on installation.

2. Verify that you have installed Oracle SQL*Net version 2.3 or higher on your client machine. Consult your Oracle manual for more information about how to install SQL*Net.

3. Include the Oracle client directory in your search path. This step depends on which version of Windows you are using, as follows:

Windows 95: Edit Autoexec.bat, and add this statement:

PATH=C:\ORAWIN95\BIN;D:\ORAWIN95;%PATH

Windows NT: Go to the Control Panel, and click on System. Select the Environment tab, and add this variable:

Variable: Path Value: C:\ORAWINNT\BIN;C:\ORAWIN

4. Close any existing applications and re-boot your system.

5. Create an Information Schema Login ID and Views by doing the following:

WARNING: Please contact your Oracle System Administrator to create these views and the privileges on the views.
This view must be created only once because table views are dynamic.

5a. Start SQL*Plus or SQL*DBA.

5b. At the SQL prompt, enter the following:

   CREATE USER INFORMATION SCHEMA IDENTIFIED BY NONE;
   GRANT SELECT ON ALL USERS TO INFORMATION SCHEMA WITH GRANT         ¬OPTION;
   GRANT SELECT ON ALL OBJECTS TO INFORMATION SCHEMA WITH GRANT               ¬OPTION;
   GRANT CONNECT TO INFORMATION SCHEMA;

5c. Log in to an Information Schema.

5d. Connect information schema/password.

5e. At the SQL prompt, enter the following:

   CREATE VIEW SCHEMATA(CATALOG NAME, SCHEMA NAME, SCHEMA OWNER)
   AS SELECT `<DB_NAME>', USERNAME, USERNAME FROM SYS.ALL USERS
   WHERE USERNAME <> `INFORMATION SCHEMA';
   GRANT SELECT ON SCHEMATA TO PUBLIC;
   CREATE VIEW TABLES(TABLE CATALOG, TABLE SCHEMA, TABLE NAME, TABLE      ¬TYPE)
   AS SELECT `<DB_NAME>', U.USERNAME, O.OBJECT_NAME, O.OBJECT_TYPE
   FROM SYS.ALL_USERS U, SYS.ALL_OBJECTS O
   WHERE U.USERNAME = O.OWNER AND
   (O.OBJECT_TYPE = `TABLE' OR O.OBJECT_TYPE = `VIEW') AND
   (O.OWNER <> `INFORMATION SCHEMA'
   AND O.OWNER <> `SYS'
   AND O.OWNER <> `SYSTEM');
   GRANT SELECT ON TABLES TO INFORMATION SCHEMA;
   COMMIT;

(The database now contains the views INFORMATION SCHEMA.SCHEMATA and INFORMATION SCHEMA.TABLES.)

NOTE: If you are using the default database, the <dbname> is Oracle.

6. Verify that you can connect successfully to your Oracle database. You can use Oracle SQL*Plus to verify the connection.

7. Run the External Database Configuration (asicfg.exe) to configure your database.

8. Add an Environment called bm_ora7. Make sure that you select Oracle7 as your DBMS List (see fig. 26.6).

Figure 26.6 When specifying a new Oracle database, a new environment name is specified in the dialog box.

9. In the General tab, enter the base value broadmoor.world for locating the server and database to which you want to connect (see fig. 26.7).

Figure 26.7 The Base value in the Environment dialog box should be the same in your TNSNAMES.ORA file.

10. Verify your TNSNAMES.ORA (SQL*Net Configuration file) setting, generated by the SQL*Net Easy configuration. Make sure that the setting matches the preceding information. In most cases, this file is located in your Oracle client installation, such as c:\orawin95\network\admin.

11. In the Environment dialog box, enter oracle as the default catalog to which you want the driver to connect upon initialization.

12. Enter 7 as your Timeout setting, which specifies the amount of time in seconds that the driver should wait for a lock to be released before informing you of the lock.

13. Click on OK to apply the changes, and close the ASI Environment dialog box.

14. After you define the environment, click on Test to verify that you successfully can connect to the Oracle database. A dialog box will indicate that you made a successful connection.

TIP: If you receive an error Operation: System Error, you probably skipped steps 3 and 4.

15. Start AutoCAD.

16. Type ASEADMIN in the Command: prompt, select your environment, Oracle7, and click on the Connect button.

17. In the Connect to Environment dialog box, enter your name and password, and then click on OK.

18. Select the Catalog button and select the name of the database you want to access.

19. Select the Schema button and select the user who has access privileges to the database you want to access.

20. Select the Table button and select the table you want to access.

You have now successfully connected to Oracle.

So far, this chapter has shown how to connect using dBase III and Oracle, for which AutoCAD supplies a direct driver. For any other databases, you need to connect using the ODBC interface. An ODBC-compliant database such as Microsoft Access will be used as an example.

Connecting Using ODBC (with Microsoft Access)

ODBC refers to Microsoft Open Database Connectivity (ODBC). ODBC is an implementation of an SQL-based database software interface designed to provide a common data access between Windows applications. You can think of ODBC as a standardized form of Dynamic Data Exchange (DDE) specifically for databases.

Two types of database drivers are supplied with AutoCAD R14: drivers that are used to connect directly to specific database applications, such as Oracle and dBase; and ODBC drivers that are used to connect to any database application with a corresponding ODBC interface. Microsoft and several other third-party developers provide ODBC software packages that contain interfaces for many databases.

The ODBC driver requires two parts: the AutoCAD-supplied ODBC driver, and an ODBC driver manager that is installed into the Operating System. The AutoCAD-supplied ODBC driver communicates with the ODBC driver manager, which in turn communicates with the database application.

To use the AutoCAD-supplied ODBC driver, you first must obtain and install an ODBC driver manager from Microsoft, a third-party provider, or the database application provider. Some 32-bit driver packages are used with Windows 95 or Windows NT, so be sure to obtain a 32-bit ODBC driver manager specific to Windows 95 or Windows NT.

Linking to ODBC-Compliant Databases

The following exercise details the procedure to link to an ODBC-compliant database such as Microsoft Access. In this case, the northwest.mdb available in the Microsoft Access sample directory is used as the sample directory.

LINKING TO MICROSOFT ACCESS

1. Obtain and install the 32-bit ODBC driver for your databases. Microsoft ODBC Driver Kit 3.0 contains several ODBC drivers, including Microsoft Access 7.0.

2. Verify that your database supports an Information Schema. If your ODBC driver does not support Information Schema, you must create one. To verify that your database supports Information Schema, refer to the database documentation.

3. Set up your MS Access database driver in the Windows ODBC Driver Manager by launching the 32-bit ODBC Administrator (odbcad32.exe). You also must set up the Access database driver. Refer to the section "Configuring Windows ODBC Driver Manager" for more information.

4. Run the External Database Configuration Editor (asicfg.exe) to configure your database. Refer to the section "Configuring Your External Database" for more information.

5. Launch AutoCAD and enter ASEADMIN in the Command: prompt.

As noted in step 1, sometimes you will need to create your own reference tables. Microsoft Access 7.0 does not support catalogs and schemas, so you must create a new database with two tables to emulate this information. When AutoCAD's ASE commands send requests through ODBC for catalog and schema data, ASE will look to this reference table to locate the actual database tables you plan to use with AutoCAD. The following steps show how to create the new table in MS Access.

NOTE:[ Refer to the following section, "Creating a Reference Table in Microsoft Access," for more information.

Creating a Reference Table in Microsoft Access

The following exercise shows the process for creating a reference table in Microsoft Access.

CREATING A REFERENCE TABLE IN ACCESS

1. Start Microsoft Access and create a new database. From the File pull-down menu, choose New Database. In this example, the database will be called infsch7.mdb and will be placed in c:\access. Type this name into the File Name edit box located on the New Database dialog box and click on the OK button.

2. The Database: INFSCH7 dialog box is displayed. Make sure the Table tab is selected and click on the New button. If the New Table dialog box is displayed, select New Table--this will bring up a new table in Design View mode. Create two fields; the first is named Catalog_Name and the second is named Schema_Name. Both are assigned Text as the data type.

3. From the File pull-down menu, choose the Save command. When prompted to save the table, name it Schemata. Click on the OK button.

4. When asked whether to create a primary key, choose No.

5. From the View pull-down menu, choose Datasheet. Insert the text Null in the Catalog Name field. Enter the full path to the database you want to use with AutoCAD in the Schema Name field. You can include as many databases as you need in the table following this format. This example (see table 26.2) uses one of the sample databases, northwind.mdb, that comes with Microsoft Access 7.0.

Table 26.2 Information to be Used for Creating the Table SCHEMATA

Catalog Name Schema Name
Null c:\access\samples\northwind

TIP: Do not include the file extension .mdb in the Schema Name field.

6. From the File pull-down menu, choose the New command, and off the cascade menu, choose Table. If the New Table dialog box is displayed, select New Table--this will bring up a new table in Design View mode. Create the four following fields, specifying the data type Text for each one.

Heading 1 Data Type
Table Catalog Text
Table Schema Text
Table Name Text
Table Type Text

7. From the File pull-down menu, choose the Save command. When prompted to save the table, name it Tables. Click on the OK button.

8. When asked whether to create a primary key, choose No.

9. From the View pull-down menu, choose Datasheet. Insert the following data for each field. Insert the text Null for the Table Catalog field, the full path to the database in the Table Schema field, the table you want to use in the Table Name field, and the text Base Table for the Table Type. Continue on the next row for the next table you plan to use in AutoCAD. For example, to use the tables Customers and Orders in the sample database northwind.mdb, the values to enter would be as follows:

Table Name Table Type Catalog Table Schema
Null c:\access\samples\northwind Customers Base table
Null c:\access\samples\northwind Orders Base table

TIP: Do not include the extension .mdb for the database file listed in the Table Schema field.

10. From the File pull-down menu, choose Save Table. Now, open the File pull-down menu and choose Exit Microsoft Access 7.0.

The process is now complete. Figure 26.8 shows the dialog boxes created in this exercise.

Figure 26.8 Sample Microsoft Access Information Schema Database.


Configuring Windows ODBC Driver Manager

Before you can connect to an SQL database using ODBC driver, you must run the 32-bit ODBC Administrator (odbcad32.exe) to configure the Windows ODBC Driver Manager. You also must set up the Access database driver. The Windows ODBC Driver Manager can be found in your Windows Control Panel.

In the driver manager, you must supply a descriptive data source name in the Data Source Name field for the Access driver, such as ODBC_Access (see fig. 26.9).

WARNING: The ODBC Data Source Name should be the same as your Environment Name.

Figure 26.9 ODBC Data Source Name definition using Windows 32-bit ODBC Administrator.


Configuring Your External Database

Before you can use your database application with AutoCAD, you must specify the databases you are using and provide the locations of the database tables. This can be done using the External Database Configuration Editor. The following exercise shows the necessary steps to configure the external database.

CONFIGURING YOUR EXTERNAL DATABASE

1. Run the External Database Configuration Editor (asicfg.exe) to configure your database. Using Windows 95 or Windows NT 4.0, select the Start button, then choose Programs, AutoCAD R14, External Database Configuration. Using Windows 3.51, double-click on the External Database Configuration icon in the AutoCAD R14 group.

2. The External Database Configuration dialog box is displayed. Click on the Add button. This displays the Select DBMS for new Environment dialog box. Under the DBMS List, select ODBC (it should be highlighted). Go to the Environment Name edit box and type ODBC_ACCESS. Go to the Environment Description edit box and type Access7 Database. Click on the OK button. Figure 26.10 shows the dialog boxes used for steps 1 and 2.

Figure 26.10 Use the External Database Configuration and Select DBMS for new Environ-ment dialog boxes to configure your database.

3. In the General tab of the Environment: ODBC_ACCCESS(ODBCDRV) dialog box, select Not Supported in the Set Schema List because Microsoft Access ODBC driver does support an information schema.

4. In the Information Schema box, enter the full path for the information schema, c:\access\infsch.mdb, to locate the database to which you want to connect (see fig. 26.11).

Figure 26.11 ODBC_ACCESS has been added to the list of environments, and the correct path name has been entered for the information schema.

5. Click on OK to apply the changes. The ASI Environment dialog box closes.

6. At this point, you should be back at the External Database Configuration dialog box and the environment is now defined. Click on the Test button to verify that you successfully can connect to Microsoft Access database. A Login dialog box is displayed. Leave the User Name edit box and the Password edit box blank. Click on the Connect button to start the test. A dialog box will indicate that you have made a successful connection. On the Connection test passed dialog box, click on the Done button, then the OK button to exit the database configuration.

TIP: If you receive an error [Microsoft] [ODBC Driver Manager] data source name not found and no default driver specified, you probably did not configure the Windows ODBC Driver Manager.

7. Launch AutoCAD and type ASEADMIN at the Command: prompt. The Administration dialog box is displayed.

8. Select the environment desired from the Database Objects area (in the case of this example, ODBC Access). After you select the name, you will notice that the Environment button is highlighted.

9. Click on the Connect button. A Connect to Environment dialog box appears, with User Name and Password fields. This information is optional. Click on OK to clear this dialog box.

NOTE: Notice that the Catalog button is grayed out. This is because MS Access 7.0 does not support this SQL2 feature.

10. Select the Schema radio button from the Database Objects selection. The database file or files listed in the Schemata table in the reference database you created earlier will appear. Highlight one of the databases listed, and select the Table radio button. The choice of database tables you supplied in the Tables table in the reference database will appear. Select the table you want to access.

You are now successfully connected to Microsoft Access via ODBC.

You now know how to connect AutoCAD to three database applications: dBase III, Oracle 7, and ODBC/Microsoft Access. Depending on which database driver you choose, you will have different capabilities. The various features and capabilities are summarized in the following section.

Understanding the Capabilities of the ASI Database Driver

Table 26.3 is a compilation of the ASI DBMS driver's capabilities, such as features and supported transactions.

Two ways exist by which to verify the capability of the database driver that you are using. In the External database configuration (asicfg.exe), select the database environment and click on the Test button. The second option is to go to the ASEADMIN dialog box, select and connect to an environment, and click on the About Env button.

Table 26.3 Comparison of dBase III, Oracle 7, and ODBC Database Driver Capabilities

Feature dBase III Oracle7 ODBC w/Access 7.0
User name Yes Yes Yes
Password Yes Yes Yes
Catalog feature Yes Yes No
Schema feature Yes Yes Yes
Time zone Yes No Yes
Character set names Yes No Yes
Translations No No Yes
Information schema facility Yes Yes Yes
Catalog definition/ No No Yes
drop catalog
Schema definition/ No No Yes
drop schema
Table definition/ Yes Yes Yes
drop table
View definition/ No Yes Yes
drop view
Index definition/ Yes Yes Yes
drop index
Translation definition/ No No Yes
drop translation
Create assertion/ No No Yes
drop assertion
Character set definition/ No No Yes
drop character set
Collation definition/ No No Yes
drop collation
Domain definition/ No No Yes
drop domain
Alter domain No No Yes
Alter table No Yes Yes
Grant/revoke privileges No Yes Yes
Commit work/rollback work No Yes No
Set transaction No Yes No
Set constraint No No Yes
Cursor manipulation (open, Yes Yes Yes
close, fetch, next)
Fetches prior, first, last, Yes Yes Yes
absolute, relative
Select statement: Yes Yes Yes
single row
Delete: positioned Yes Yes Yes
Update: positioned Yes Yes No
Delete: searched Yes Yes No
Update: searched Yes Yes Yes
Insert Yes Yes Yes

Converting Release 12 ASE Links to R14 ASE Links

AutoCAD Release 12 supports the original SQL standard format. With AutoCAD Releases 13 and 14, ASE was enhanced by supporting the SQL2 database standard format. This format provides benefits such as simultaneous access to several DBMSs.

The adoption of SQL2 format involves conceptual and environmental changes, including the terms environment, catalog, and schema. These changes in environment also change how ASE defines the database data, as well as its hierarchy, structure, and the methods of access. Due to these changes, R12 ASE links cannot be used in AutoCAD Releases 13 and 14 without conversion.

The following exercise shows how to perform this conversion process. The following R12 tutorial drawing and its databases are used in the exercise. These files are normally found in the tutorial directory of AutoCAD 12:

c:\acad12\tutorial\asetut.dwg
c:\acad12\tutorial\dbf\employee.dbf
c:\acad12\tutorial\dbf\computer.dbf
c:\acad12\tutorial\dbf\inventry.dbf

AutoCAD Release 14 can read AutoCAD Release 12 ASE link and convert it into AutoCAD Release 14 format. To execute this conversion, an equivalence must take place between Release 12 link terms (DBMS, database, and table) and Release 14 link terms (environment, catalog, schema, table, and link path name (LPN)). The old DBMS name is mapped to the new environment name using the same DBMS driver. The old database name is mapped to the new catalog and schema names. The old tables are mapped to new tables and link path names. Figure 26.12 illustrates the terms and relationships used in R12 with corresponding R14 equivalents.

Figure 26.12 R14 uses different ASE terms than R12.

The table name typically is the same table used with the AutoCAD Release 12 drawing. Invalid links resulting from missing linked rows or key columns that have changed will be removed during conversion.

CONVERTING R12 ASE LINKS TO R14 ASE FORMAT

1. Use the External Database Configuration (asicfg.exe) to configure your database. If you are unfamiliar with this process, see the previous section for more information.

2. In the External Database Configuration dialog box, click on the Add button. The Select DBMS for new Environment dialog box is displayed. Under the DBMS List, select dBase III (it should be highlighted). Go to the Environment Name edit box and type R12_DB3. Go to the Environment Description edit box and type Convert R12 Links. Click on the OK button to save the Environment name.

3. The Environment:R12_DB3(DB3DRV) dialog box is displayed. If the Catalog tab is not showing, select Catalog. Go to the Name edit box in the Catalog section and type R12DB3.

4. Go to the Path edit box in the Catalog section and enter the path c:\acad12\ tutorial.

5. Click on the New button in the Catalog section to save the new Catalog Name.

6. Go to the Name edit box in the Schema section and type dbf as the schema name.

7. Go to the Path edit box in the Schema section and type dbf as the Schema Path.

8. Click on the New button in the Schema section to save the new schema name. Click on the OK button to exit the Environment dialog box.

9. In the External Database Configuration dialog box, select the Convert R12 Links tab, if it's not already displayed (see fig. 26.13).

Figure 26.13 The Convert R12 Links tab of the External Database Configuration dialog box is used to map R12 Link information with the new hierarchy in R14.

10. Under R12 Link, specify the following for each of the identified edit boxes:

DBMS: dBase3

Database: asetut

Table: employee

11. Under R14 Link, specify the following for each of the identified edit boxes:

Environment: R12_DB3

Catalog: R12DB3

Schema: dbf

Table: employee

Link Path Name: empnum

12. Click on the New button to save the conversion of your R12 Link to R14 Link. An ASI Editor dialog box will appear to confirm that you want to proceed with the conversion (see fig. 26.14). Click on the OK button to proceed. Click on the OK button at the bottom of the External Database Configuration dialog box to exit the Database Configuration utility.

Figure 26.14 A dialog box appears to confirm whether you want to proceed with the conversion.

13. Start AutoCAD and use the OPEN command to open c:\acad12\tutorial\asetut.dwg.

14. Type ASEADMIN at the Command: prompt. An ASE Warning dialog box is displayed. This lists errors caused by the existence of R12 formatted links in the drawing. Ignore these errors for now and click on the Close button. The Administration dialog box is displayed. In the Database Objects scroll window, select the environment R12_DB3, and click on the Connect button, as shown in figure 26.15. In the Connect to Environment dialog box, leave the name and password fields blank; then click on the OK button. Click on the Catalog button in the Database Object Selection area, then highlight R12_DB3 in the Database Objects scroll window. Click on the Schema button in the Database Object Selection area. Highlight DBF in the Database Objects scroll window. Click on the Table button in the Database Object Selection area.

Figure 26.15 The R12_DB3 environment successfully converted to Release 14 format.

15. Highlight the EMPLOYEE table in the Database Objects scroll window, and click on the Synchronize button (see fig. 26.16).

Figure 26.16 The Administration dialog box displaying existing tables that need to be synchronized.

16. A dialog box will appear with a warning message, as shown in figure 26.17.

Figure 26.17 A warning message appears stating that the table has the wrong key type.

17. Click on the Select All button to select all the messages, and click on the Synchronize button.

NOTE: This procedure is necessary for the proper conversion of the values or to delete any invalid links. If there is no warning message, then all your R12 information was converted successfully.

18. If no warning messages are displayed then click on the Close button to exit the Synchronize Links dialog. Click on the OK button to exit the Administration dialog.

19. Each table must be synchronized. Therefore, you must go through steps 1 through 18 for both the COMPUTER and the INVENTRY tables. In the preceding steps, replace the table name EMPLOYEE with the appropriate table name to synchronize.

NOTE: If a DATE column was used to define key values in the Release 12 drawing and the driver mapped the data type to CHAR, the links to those objects might not get converted and could be lost.

After you synchronize each table, the links should successfully be updated to R14.

Understanding the Features and Functions of Structured Query Language (SQL)

SQL, which stands for Structured Query Language, has become the standard computer database language. The computer program that controls the database, (such as Microsoft Access and Oracle 7) is called the database management system. SQL is a comprehensive language for controlling and interacting with a database management system (DBMS), and this tool can organize, manage, and retrieve data stored by a computer database. In fact, SQL works with one specific type of database, called a relational database. Figure 26.18 shows a visual representation of how SQL works.

Figure 26.18 How SQL is used as a tool to request database information.

When you need to retrieve data from the database, you can use the SQL language to make the request. The DBMS processes the SQL request, retrieves the requested data, and returns it to you. This process of requesting data from the database and receiving results is called a query (hence the name Structured Query Language).

This section contains a quick tour of SQL to illustrate its major features and functions. Included is a sample database to help give you basic familiarity with the SQL language.

NOTE: For detailed information on how to try sample SQL statements from within AutoCAD, see the last exercise in this chapter, "Searching for Database Data Using AutoCAD SQL Editor."

Reviewing a Sample Database: Facilities Management Database

The sample database (bldg18.dbf) in this exercise is a database of an office building detailing the location, asset number, and cost of all computer equipment. The data also includes the room number, equipment manufacturer, and department assignment. The sample database can be found on the CD.

Table 26.4 Bldg18.dbf Sample Database

Room Manufacturer Description EQ_Type Department Asset No. Cost
42202 Panasonic Omnivision A/V VCR Multimedia 45089 2600.6
PV-4451
42203 Panasonic Superflat A/V TV Multimedia 45093 2600.6
CT27SF11S Color
42214 Toshiba 386 Portable CPU Port Field 35662 8656.86
T2200SX Engineering
60 MB
42214 Micron 586/90 CPU Graphic 55653 0
Deskpro 590 design
42216 Micron 90 CPU Graphic 55654 0
Deskpro 590 design
42216 Micron 386 20 E CPU Accounting 8738 9822.24
42217 NEC 3D Monitor Accounting 8737 658.6

Retrieving Data from the Database Using the SELECT Statement

The SQL statement that retrieves data from the database is called SELECT. If you want to list the room number, equipment manufacturer, and the department, the appropriate statement would be:

SELECT ROOM, MANUFACTURER, DEPARTMENT FROM BLDG18

Table 26.5 displays the results.

Table 26.5 Sample Formatted Output Resulting from Executing the Preceding SQL Statement

ROOM MANUFACTURER DEPARTMENT
42202 Panasonic Multimedia
42203 Panasonic Multimedia
42214 Toshiba Field engineering
42214 Micron Graphic design
42216 Micron Graphic design
42216 Micron Accounting
42217 NEC Accounting

The following list details other valid Select statements:

Comparison Operators in SQL Syntax

SQL statements contain comparison operators, which are symbols used to describe certain conditions for data. Some of these operators are used in the bulleted list in the preceding section.

The comparison operators and their descriptions are contained in table 26.6.

Table 26.6 Comparison Operators for SQL Syntax

Operator Description
= Equals
> Greater than
< Less than
>= Greater than or equal to
<= Less than or equal to
<> Not equal to
BETWEEN.. Between two values
AND..
IN (list..) Any values in the specified list
LIKE Match a character pattern
IS NULL Where the value is null (no value)

Adding Data to the Database Using the INSERT Statement

To create a new record in a table, you must insert the column values by using the SQL statement INSERT. For example, if you want to add a new record in the sample database containing the room number, equipment manufacturer, equipment type, and department, you would type:

INSERT INTO BLDG18 (ROOM, MANUFACTURER, EQ TYPE, DEPARTMENT)
VALUES (`42220', `COMPAQ', `CPU', `MULTIMEDIA')

Updating the Database Using the UPDATE Statement

Column-specific data can be changed using the SQL statement UPDATE. Columns can be updated individually or globally. For example, to change the cost of the monitor to 8,850, you would type:

UPDATE BLDG18 SET COST = 8,850 WHERE EQ TYPE = `MONITOR'

Deleting Data from the Database Using DELETE Statement

Records can be removed using the SQL statement DELETE. Records can be deleted individually or globally. For example, if you want to delete any record(s) that contains equipment from Apple, enter the following:

DELETE FROM BLDG18 WHERE MANUFACTURER = `APPLE'

Keep the following points in mind when using the DELETE statement:

Creating a Database Table Using CREATE TABLE Statement

Before you can store data in a database, you first must define the structure of the data. To continue with the example of the Facilities Management database, suppose you want to expand the bldg18.dbf database by adding table data on employee information for each room number. The following list details the data to be stored for each room:

This SQL Create Table statement defines a new table in which to store the products data: Create Table Employee (Room Integer, Last Name Char[25], First Name Char[20], Title Char[20]).

Calculating Data in the Database

SQL enables you to summarize data from the database through a set of columns. Table 26.9 details some SQL statements that are useful in calculating database information.

Table 26.9 SQL Statements Used to Calculate Database Information

SQL Statement Description
Select Avg(Cost) from BLDG18 Calculates the average cost of the equipment in bldg18 database
Select Sum(Cost) from BLDG18 Calculates the total cost of the equipment in bldg18 database
Select Min(Cost) from BLDG18 Calculates the lowest equipment cost in bldg18 database
Select Max(Cost) from BLDG18 Calculates the highest equipment cost in bldg18 database

Summary of SQL Syntax

This section has included a lot of information on statements and syntax that can be used to manipulate databases. For quick reference, table 26.10 summarizes the major SQL statements with which you should be familiar.

Table 26.10 Major SQL Statements Used in Data Manipulation and Definition

Statement Description

Data Manipulation

Select Retrieves data from the database
Insert Adds new rows of data to the database
Delete Removes rows of data to the database
Update Modifies existing database data
Create Table Adds a new table to the database
Drop Table Removes a table from the database
Alter Table Changes the structure of an existing table
Create Index Builds an index for a column
Drop Index Removes the index for a column

So far, you have learned to connect to various databases and include links to databases within your AutoCAD drawings. This chapter also has covered SQL syntax so that you will be able to manipulate data in your database effectively. The following section takes the next step; it discusses how to make productive use of AutoCAD objects and the external database together.

Using ASE Effectively

After you establish your database link in your AutoCAD drawings, the next step to consider is how to combine and use an AutoCAD object and its external database together. This section brings together all the information covered thus far and details the entire process of connecting to external databases and using ASE effectively.

The exercises in this section continue to use the bldg18.dbf database to demonstrate how the database information inside an AutoCAD drawing could be used. To refresh your memory, this is a database of an office building detailing the location, asset number, and cost of the computer equipment. This information includes the room number, equipment manufacturer, and department assignment for each piece of equipment.

Specifically, this section describes how to accomplish the following tasks:

The task of setting up the database and creating and using the links within AutoCAD can be divided into six separate exercises corresponding to the steps in the preceding list.

Step 1: Setting Up the Database

In this example, the drawing and the database reside on the accompanying CD-ROM.

SETTING UP THE EXTERNAL DATABASE

1. Use the External Database Configuration (asicfg.exe) to configure your database. If you are unfamiliar with this process, see the previous section for more information.

2. In the External Database Configuration dialog box, click on the Add button. The Select DBMS for new Environment dialog box is displayed. Under the DBMS List, select dBase III (it should be highlighted as shown in figure 26.19). Go to the Environment Name edit box and type Project18. Go to the Environment Description edit box and type JG Additional Project. Click on the OK button to save the Environment name.

3. The Environment:Project18(DB3DRV) dialog box is displayed. Go to the Name edit box in the Catalog section and type Bldg18. Go to the Path edit box in the Catalog section and enter the path d:\proj18. Click on the New button in the Catalog area to save the new Catalog Name.

Figure 26.19 Dialog box for defining your Environment Name after selecting the appropriate DBMS.

4. Go to the Name edit box in the Schema section and type Facilities as the schema name. Go to the Path edit box in the Schema section and type facmgmt as the Schema Path. Click on the New button in the Schema section to save the new schema name (see fig. 26.20).

Figure 26.20 The Catalog tab of the Environment dialog box is used to specify the catalog and schema information.

5. Click on OK to apply the changes, and close the ASI Environment dialog box.

6. After you define the environment, click on Test to verify that you successfully can connect to proj18.dbf. A Login dialog box is displayed. Leave the User Name edit box and the Password edit box blank. Click on the Connect button to start the test. A dialog box will indicate that you have made a successful connection. On the Connection test passed dialog box, click on the Done button, then the OK button to exit the database configuration.

Step 2: Setting Up Your DBMS and Database

After you configure your database, you must tell AutoCAD which DBMS you are using and where to find the databases. This step is facilitated by the ASEADMIN command.

SPECIFYING THE DBMS AND DATABASE USING THE ASEADMIN COMMAND

1. Start AutoCAD and use the Open command to open d:\proj18\facmgmt\bldg18.dwg.

2. Type ASEADMIN at the Command: prompt. The Administration dialog box appears, as shown in figure 26.21.

Figure 26.21 The Administration dialog box appears when you enter ASEADMIN at the Command: prompt.

3. In the Database Objects scroll window, scroll down the list and select the environment Project18, and click on the Connect button. In the Connect to Environment dialog box, leave the name and password fields blank; then click on the OK button. Click on the Catalog button in the Database Object Selection area. Highlight Bldg18 in the Database Objects scroll window. Select the Schema button in the Database Object Selection area. Highlight Facilities in the Database Objects scroll window. Select the Table button in the Database Object Selection area. Highlight Bldg18 in the Database Objects scroll window as shown in figure 26.22.

Figure 26.22 Multiple tables are displayed for every database environment defined.

4. Select the Link Path Name button at the bottom of the Administration dialog box. The Link Path Names dialog box is displayed. In the Key Selection area, select Asset_No (that row will highlight), and then click on the On button.

5. In the Link Path area, go to the New edit box and type Asset No. (Note, do not include the period as part of the name.)

6. Make sure that you click on the New button to register the new link path name successfully (see fig. 26.23). Click on the Close button to exit the Link Path Name dialog box. Click on the OK button to exit the Administration dialog box.

Figure 26.23 Creating Link Path Name by selecting the unique identifier for the database.

Step 3: Setting Up Your Database Links with AutoCAD Objects

One of the most powerful features of ASE is the link that you can create between a graphic entity and a row in a table. This link enables you to create a two-way data flow: data changed in the table can update the values of displayable attributes in a drawing, and AutoCAD can change data stored in the table.

The process of making the object data links with the AutoCAD objects can be both manual and automated. Linking from the Rows dialog boxes involves selecting a specific record and then picking the AutoCAD object. You can link multiple entities to the same row, and you can link multiple rows to the same entity. Often, rows from multiple tables link to one entity. The only restriction is that each row must have a unique key value.

This exercise links bldg18.dbf using the Asset No. column as the primary key because the asset number is unique to each record.

LINKING AN OFFICE FLOOR PLAN DRAWING WITH THE BUILDING DATABASE

1. Continuing with the bldg18.dwg drawing, type ASEROWS at the Command: prompt. The Rows dialog box is displayed. In the Condition edit box within the Select Rows area type ROOM=42317, as shown in figure 26.24. This specifies the room number to which you want to link. Here, the Room value 42317, which is located on the left portion of the drawing, is used.

Figure 26.24 The room number is entered in the Rows dialog box to search the database for each occurrence of Room 42317.

2. Press Enter to confirm the value you specify. ASE will return all occurrences of Room 42317 in the database. In this example, nine records belong to this room.

3. Click on the Make Link button to link the current record to the AutoCAD object that matches the room number 42317. The dialog box is temporarily cleared and you're prompted to select the graphic object that will be linked to the current database row. Select the AutoCAD text object identifying room 42317. Press the Enter key to end object selection. The Rows dialog box will reappear (see fig. 26.25) and if the link is successfully created, a confirmation message saying 1 link(s) created will appear at the bottom of the dialog box.

Figure 26.25 The ASE Rows dialog box appears displaying the database information linked to the AutoCAD objects.

4. Click on the Next button to go to the next record to which you want to link. Repeat step 3 to create the link. You can repeat the preceding steps if you want to link with more AutoCAD objects.

5. To verify that the link was created, type ASELINKS at the Command: prompt and select the object that contains the link.

6. In the Links dialog box, click on the Row button to view the associated database information (see fig. 26.26).

Figure 26.26 The Rows dialog box shows the associated database information.

Step 4: Displaying Data in the Drawings

After you link your external database to your AutoCAD drawing, you can prompt ASE to display the data. Using the links set in a drawing, you can display column data in the drawings, display data using graphics selections, and select entities based on a table query.

This can be accomplished in ASE using the Displayable Attributes function in ASEROWS. This function enables you to display the value of a particular row and column in the drawing. The user must determine how the row and column will appear.

Continuing with the current example, use the steps in the following exercise to display, on the AutoCAD drawing, the information linked to Room 42317.

DISPLAYING THE INFORMATION LINKED TO ROOM 42317

1. Continuing with the bldg18.dwg drawing, type ASEROWS at the Command: prompt. Click on the Graphical button.

2. When prompted to select the object, select the object 42317. The screen should display the current record linked to that object.

3. Click on the Make DA button to create the displayable attributes.

4. In the Make Displayable Attribute dialog box, identify each column to display by selecting each column in the Table Columns list box and by clicking on the Add button or double-clicking on the desired column (see fig. 26.27).

Figure 26.27 Select columns to display using the Make Displayable Attribute dialog box.

5. Once all the columns are identified, adjust text object characteristics in the Format area as desired. Click on the OK button and identify the insertion point of the attributes. The Rows dialog box reappears. Click on the OK button to exit the dialog box. The displayable attributes will now be visible on the drawing. (See figure 26.28 for the resulting display.)

Figure 26.28 Room 42317 in the AutoCAD drawing displays the information contained in the selected columns.

Step 5: Adding or Deleting Database Records Inside AutoCAD

Now that you have linked your external database to your AutoCAD drawing, the database information is available within your current drawing session. With ASE, you do not need a DBMS system to manipulate the database information. ASE provides a two-way data flow, manipulating the graphic objects from the table and manipulating the table from the graphic objects.

Suppose you have a new Compaq computer and must add the information in the database. The following exercise shows the steps involved in adding a new database record.

ADDING A DATABASE RECORD

1. Continuing with the bldg18.dwg drawing, type ASEROWS at the Command: prompt. Select Updatable in the Cursor State area.

2. Click on the Edit button. The Edit Row dialog box appears. This dialog box serves as a template that lists all the columns in which you must enter the information, as shown on figure 26.29.

Figure 26.29 The Edit Row dialog box enables you to create or modify external database information.

3. Type the column's data into the Value edit box and press the Enter key. Repeat this for each column until the template is filled out.

4 Click on the Insert button to insert the row in your database. The message Row is inserted appears at the bottom of the Edit Rows dialog box. Click on the Close button and then click on the OK button to exit the Rows dialog box.

Next, suppose you want to delete all the obsolete equipment in your office, such as the Canon equipment. To delete a database record, follow these steps:

DELETING A DATABASE RECORD

1. Continuing with the bldg18.dwg drawing, type ASEROWS at the Command: prompt. Select Updatable in the Cursor State area.

2. In the Condition edit box in the SELECT Rows area, type manufactur=`CANON' to specify the equipment you want to delete in the database.

3. Press Enter to confirm the value you specify. ASE will return all occurrences of all equipment manufactured by Canon in the database. In this example, one record belongs to Canon.

4. Click on the Edit button. The Edit Row dialog box appears with the current record information, as shown in figure 26.30.

Figure 26.30 The ASE Edit Rows dialog box appears containing the search result executed in the ASE Rows dialog box.

5. Click on the Delete button, and click on OK to confirm the deletion of the current record. The message Row is deleted appears at the bottom of the Edit Rows dialog box.

TIP: If the Delete button is disabled, the Cursor State is set as Read-Only. You must change the Cursor State to Updatable to be able to edit your database.

6. Click on the Close button on the Edit Row dialog box. The record disappears from the Rows dialog box. Click on the OK button to exit the ASEROWS command.

Step 6: Searching Database Data Using AutoCAD SQL Editor

You can use the AutoCAD SQL Editor to search for data using SQL statements as your criteria. For example, the following exercise shows the steps involved in searching for all the Apple Computer equipment.

SEARCHING FOR DATABASE DATA USING AUTOCAD SQL EDITOR

1. Continuing with the bldg18.dwg drawing, type ASESQLED at the Command: prompt. The SQL Editor dialog box is displayed. In the SQL edit box within the SQL Statement area, type select * from bldg18, where manufactur=`APPLE'.

2. Click on the Execute button to process the SQL statement. As shown in figure 26.31, the computer will return all the records that match the SQL criteria you specified.

Figure 26.31 The SQL Cursor dialog box displays the search result executed from the SQL Editor dialog box using an SQL statement.

Dealing with Common Problems and Error Messages

This section discusses some of the common problems and error messages that can occur when using ASE. Each of the following is an explanation of the possible reasons or suggested ways to solve the problem.

Summary

This chapter explained how to get the most out of the AutoCAD SQL environment. In addition to explaining how to connect to dBase III, Oracle 7, and ODBC (Access) database applications, this chapter provided an overview of the basics of SQL syntax. Exercises were provided to help you understand how to use ASE effectively to link to and display database information. Finally, common error messages and problems were presented, along with explanations and suggestions on how to solve them.

Previous chapterNext chapterContents

© Copyright, Macmillan Computer Publishing. All rights reserved.